declare @HoP table
(
	 ID int,
	ChamberName varchar(25) 
);
insert into @HoP
values( 0, 'House of Representatives');
insert into @HoP
values( 1, 'Senate');

DECLARE @UnknownPartyId int = 99;
DECLARE @UnknownPartyName varchar(7) = 'UNKNOWN';

begin try

begin tran t1;

	delete PARL2.dbo.ParliamentarianPositions;
	delete PARL2.dbo.ElectorateOffices;
	delete PARL2.dbo.Positions;
	delete PARL2.dbo.Parliamentarians;
	delete PARL2.dbo.Parties;

	-- PARTIES --
	insert into PARL2.dbo.Parties
		(RecID, FullName, ShortName, Precedence, AbbreviatedName)
		Select
			RecID = p.RecID,
			FullName = p.PartyInfoFull,
			ShortName = p.PartyInfoBrief,
			Precedence = p.PartyPrecedence,
			AbbreviatedName = p.PartyInfoAbbreviation
		From CPALPARL2.dbo.tblPartyInformation as p;

	-- PARTIES - UNKNOWN IDNETIFIER for those parties (such as AD)
	--				that are no longer present in the master list
	insert into PARL2.dbo.Parties
		(  RecId, FullName, ShortName, Precedence, AbbreviatedName )
		VALUES ( @UnknownPartyId, @UnknownPartyName, @UnknownPartyName, 99, @UnknownPartyName);


	--PARLIAMENTARIANS --
	insert into PARL2.dbo.Parliamentarians
	( CPALId, Title, FirstName, Surname, Honorific, Electorate, State,
		Chamber, Telephone, Fax, EmailAddress, FacebookAddress, TwitterAddress, PersonalWebsite, PartyWebsite,
		MPID, FormerMember, Gender, PreferredName, PartyRecId, OtherNames)
		Select 
			m.RecID,
			m.MPNameTitle,
			m.MPFirstName,
			m.MPSurname,
			m.MPHonorific,
			m.MPElectorate,
			m.MPState,
			h.ChamberName,
			m.MPTelephone,
			m.MPFax,
			m.MPInternet,
			null, null, null, null,
			m.MPNeoNameID,
			m.MPFormer,
			m.MPGender,
			m.MPPreferredName,
			CASE 
				WHEN p.RecID is null then @UnknownPartyId
				ELSE p.RecID
			END,
			m.MPOtherNames
		From CPALPARL2.dbo.tblMPsInformation as m 
			left join @HoP as h on m.MPType = h.ID
			left join CPALPARL2.dbo.tblPartyInformation as p on m.MPPolParty = p.PartyInfoAbbreviation
		--WHERE m.MPPolParty is not null and p.RecID is not null;

	insert into PARL2.dbo.Positions
	(Id, FullTitle)
	select
		ti.RecID,
		ti.TitleInfoFull
	from CPALPARL2.dbo.tblTitleInformation as ti;


	Insert into PARL2.dbo.ParliamentarianPositions
		( Parliamentarian_CPALId, Positions_Id )
		select t.RecID, t.Title
		from CPALPARL2.dbo.tblTitle as t join 
				CPALPARL2.dbo.tblTitleInformation as ti
				on t.Title = ti.RecID
		where t.RecID is not null AND  t.Title is not null AND ti.TitleInfoFull is not null;
	
	select 
		o.ElectorateAddressLine1,
		o.ElectorateAddressLine2,
		o.ElectorateSuburb,
		o.ElectorateState,
		o.ElectoratePostcode,
		o.ElectoratePrincipalOffice,
		o.ElectorateTelephone,
		o.ElectorateFax,
		o.LabelAddress,
		o.LabelSuburb,
		o.LabelState,
		o.LabelPostcode,
		counter = 0,
		p.RecID,
		o.ElectorateTollFree
		into #ElectorateOffices_temp
	from CPALPARL2.dbo.tblElectorateOfficeDetails as o
		left join CPALPARL2.dbo.tblMPsInformation as p on o.RecID = p.RecID
	where p.MPPolParty != '';
	
		-- Ids for ElectorateOffices do not exist in CPAL
	DECLARE @counter int = 0;
	UPDATE #ElectorateOffices_temp
	SET @counter = counter = @counter + 1;
	
	insert into PARL2.dbo.ElectorateOffices
	(AddressLine1,AddressLine2,Suburb,State,PostCode,PrincipalOffice,
	 Telephone,Fax,PostalAddress,PostalSuburb,PostalState,PostalPostCode,
	 Id,Parliamentarian_CPALId,TollFreePhone)
		select * from #ElectorateOffices_temp;;

	DROP table #ElectorateOffices_temp;
	
	-- Parliamentarian Links

	USE PARL2;

	Update PARL2.dbo.Parliamentarians  
	set PersonalWebsite =		wlinks.PersonalWebsite,
		PartyWebsite =			wlinks.PartyWebsite,
		FacebookAddress =		wlinks.FacebookAddress,
		TwitterAddress =		wlinks.TwitterAddress,
		EmailVisible =			wlinks.EmailVisible,
		ContactFormVisible =	wlinks.ContactFormVisible,
		Link1Address =			wlinks.Link1Address,
		Link2Address =			wlinks.Link2Address,
		Link3Address =			wlinks.Link3Address,
		Link4Address =			wlinks.Link4Address,
		Link5Address =			wlinks.Link5Address,
		Link1Label	=			wlinks.Link1Label,
		Link2Label =			wlinks.Link2Label,
		Link3Label =			wlinks.Link3Label,
		Link4Label =			wlinks.Link4Label,
		Link5Label =			wlinks.Link5Label,
		ElectionMessage =		wlinks.ElectionMessage,
		FirstSpeechLink =		wlinks.FirstSpeechLink,
		ShortURL =				wLinks.ShortURL
	from ParliamentarianWebLinks as wlinks
			where wlinks.CPALId = Parliamentarians.CPALId;
	
	commit tran t1;
	PRINT 'PARL2 Data Update Process successful.'
	
End try
Begin Catch
	-- Rollback
	rollback tran t1;

	DECLARE @ErrorMessage varchar(max) = ERROR_MESSAGE();
	DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
	DECLARE @ErrorState INT = ERROR_STATE();
				

	PRINT 'PARL2 Data Update Process unsuccessful.'

	-- throw the error so job can respond appropriately
	Raiserror( @ErrorMessage,@ErrorSeverity,@ErrorState);

	/**
	use master 
	go
	EXEC master.dbo.xp_sendmail 
    	@recipients=N'sql.services@aph.gov.au',
    	@message=N'PARL2 Table load failed.' ;
	Go
	**/


End Catch 
